﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using CinemaProject;

namespace CinemaDbLib
{
    class MovieDB
    {
        private Connection connection;

        public MovieDB()
        {
            connection = new Connection();
        }

        public List<Movie> getMovies()
        {
            return where("");
        }

        public List<Movie> getMovieSearch(string title)
        {
            return where("name LIKE " + title);
        }

        public Movie getMovieById(int id)
        {
            return singleWhere("id = " + id);
        }

        public void deleteMovie(int id)
        {
            string query = "DELETE FROM XpMovie WHERE id=" + id;
            
            connection.Open();
            connection.Execute(query);

            connection.Close();
        }


        public void updateMovie(Movie movie)
        {
            string query = @"UPDATE Movies SET review='" +
                movie._review + "', description='" + movie._description +
                "', director='" + movie._director +
                "', genre='" + movie._genre + 
                "', length='" + movie._length +
                "' WHERE id=" + movie._id;
            
            connection.Open();
            connection.Execute(query);
            
            connection.Close();
        }

        public void insertMovie(Movie movie)
        {
            string query = @"INSERT INTO XpMovies (description, title, review, length, genre, director) " +
                "VALUES (" + movie._description + "', '" + movie._title + "', '" + "', '" + movie._review + "', '" + "', '" + movie._length + "', '" + "', '" + movie._genre + "', '" + "', '" + movie._description + "')";

            connection.Open();

            connection.Execute(query);

            connection.Close();
        }

        private Movie singleWhere(String wClause)
        {
            Movie movie = null;

            string query = "SELECT * FROM Movies WHERE " + wClause;  
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            if (reader.Read())
            {
                movie = new Movie();

                movie._id = reader.GetInt32(0);
                movie._title = reader.GetString(1);
                movie._description = reader.GetString(2);
                movie._length = reader.GetDecimal(3);
                movie._review = reader.GetString(4);
                movie._director = reader.GetString(5);
                movie._genre = reader.GetString(6);
            }

            reader.Close();
            connection.Close();

            return movie;
        }

        private List<Movie> where(String wClause)
        {
            List<Movie> movies = new List<Movie>();
            string query;
            if (!wClause.Equals(""))
                query = "SELECT * FROM XpMovies WHERE " + wClause;
            else
                query = "SELECT * FROM XpMovies";
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            while (reader.Read())
            {
                Movie movie = new Movie();

                movie._id = reader.GetInt32(0);
                movie._title = reader.GetString(1);
                movie._description = reader.GetString(2);
                movie._length = reader.GetDecimal(3);
                movie._review = reader.GetString(4);
                movie._director = reader.GetString(5);
                movie._genre = reader.GetString(6);

                movies.Add(movie);
            }

            reader.Close();
            connection.Close();

            return movies;
        }
    }
}
